USE BMIG_Mensual_POST_CADENA
go

IF OBJECT_ID('dbo.WASP_M0016_Genera_table') IS NOT NULL 
   BEGIN
      DROP PROCEDURE dbo.WASP_M0016_Genera_table 
      IF OBJECT_ID('dbo.WASP_M0016_Genera_table') 
      IS
      NOT NULL 
         PRINT '<<< FAILED DROPPING PROCEDURE dbo.WASP_M0016_Genera_table >>>' 
      ELSE
         PRINT '<<< DROPPED PROCEDURE dbo.WASP_M0016_Genera_table >>>' 
   END
go

SET ANSI_NULLS ON
go

SET QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE WASP_M0016_Genera_table
AS
   BEGIN
      SELECT a.nombre_trabajador,
         a.rut_trabajador,
         a.Fecha_nacimiento,
         a.sexo,
         c.cuotas,
         val_seguro = 	 	
         CASE WHEN (a.aopap = 2 AND a.aomda = 999)THEN 	 	 	 
                    cast((b.val_seg / 100) * aopre AS numeric         ) 	 	
         ELSE
            cast(b.val_seg AS numeric) 	 	
         END,
         cast('         ' AS varchar(8)) AS fecha_colocacion,
         cast('         ' AS varchar(8)) AS fecha_ult_venc,
         a.aosuc AS suc_colocacion,
         a.aooper numero_credito,
         cast(' ' AS varchar(40)) AS direccion,
         cast('         ' AS varchar(9)) AS telefono			 
      INTO dbo.sv_seg_vida 
      FROM 	as_seg_vida_temp AS a, 
      --  	dbo.sv_seguro_vida_tra as b,
      --	dbo.as_cuotas_temp as c
      sv_seguro_vida_tra AS b LEFT JOIN as_cuotas_temp AS c 	ON 	b.ppsuc = c.ppsuc
      AND 	b.ppoper = c.ppoper 
      AND 	b.ppcta = c.ppcta 
      WHERE 	b.ppsuc = a.aosuc 
      AND 	b.ppoper = a.aooper 
      AND 	b.ppcta = a.aocta 	
      /*b.ppsuc *= c.ppsuc and
      	b.ppoper *= c.ppoper and
      	b.ppcta  *= c.ppcta */

      UPDATE sv_seg_vida 
      SET fecha_colocacion = cast(CONVERT(varchar(8), AOFVAL, 112) AS decimal) 
      FROM fsd010 
      WHERE suc_colocacion = aosuc 
      AND numero_credito = aooper 
      AND aosbop = 0 
      
      UPDATE sv_seg_vida 
      SET fecha_ult_venc = cast(CONVERT(varchar(8), PPFPAG, 112) AS decimal) 
      FROM fsd601 
      WHERE suc_colocacion = ppsuc 
      AND numero_credito = ppoper 
      
      
      UPDATE sv_seg_vida 
      SET DIRECCION = cast(DOCALLP AS varchar) + ' ' + cast(DONROP AS varchar) 
      FROM fsd005, sv_seg_vida 
      WHERE Pendoc = rut_trabajador 
      
      UPDATE sv_seg_vida 
      SET telefono = SUBSTRING(RTRIM(LTRIM(Dotelfp)), 1, 9) 
      FROM fsr005, sv_seg_vida 
      WHERE LTRIM(RTRIM(Pendoc)) = rut_trabajador 
      AND Doordp >= 1 
      AND Doordp <= 15 
   END
go



SET ANSI_NULLS OFF
go

SET QUOTED_IDENTIFIER OFF
go

IF OBJECT_ID('dbo.WASP_M0016_Genera_table') IS NOT NULL 
   PRINT '<<< CREATED PROCEDURE dbo.WASP_M0016_Genera_table >>>' 
ELSE
   PRINT '<<< FAILED CREATING PROCEDURE dbo.WASP_M0016_Genera_table >>>'
go
